//    Copyright (C) Kherty.  All rights reserved.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Diagnostics;
using System.Diagnostics.Contracts;

using OpenLS.Spreadsheet.Extensibility;

namespace OpenLS.Spreadsheet.StandardAddIn
{
    [AddInFunctions]
    public static class LookupFunctions
    {
        public static object Address([WorksheetContext]IWorksheet worksheet, int row_num, int column_num, [Optional(1)] int abs_num, [Optional(true)] bool A1,
                                     [Optional(null)] string sheet_text)
        {
            return worksheet.Address(row_num, column_num, abs_num, A1, sheet_text);
            
        }

        public static int Areas([AllowCellToRangeConversion] params IRange[] range)
        {
            int result = 0;
            foreach (var r in range)
                result += r.Areas;
            return result;
            /*if (range is CompositeRange)
                return ((CompositeRange) range).Ranges.Count;
            return 1;*/
        }

        public static object Choose(double index_num, [NoConversion]params object[] values)
        {
            var actualIndex = (int) Math.Floor(index_num);
            if (actualIndex < 1 || actualIndex > values.Length)
                return Errors.ValueError;
            return values[actualIndex - 1];
        }

        [NoValueAccess]
        public static int Column([CellContext]ICell context, [AcceptsRangeFirstCell, Optional(null)] ICell cr)
        {
            ICell cell = cr ?? context;
            return cell.ColumnIndex + 1;
        }

        [NoValueAccess]
        public static object Columns(IArray array)
        {
            return array.ColumnCount;
        }

        public static object GetPivotData(string data_field, [AllowCellToRangeConversion]IRange pivot_table, params object [] fieldsAndItems)
        {
            return pivot_table.Worksheet.GetPivotData(data_field, pivot_table, fieldsAndItems);
        }
        static IEnumerable<KeyValuePair<int, object>> GetRowValues(IArray array, int row)
        {
            if (array is IRange)
            {
                IRange range = (IRange)array;
                foreach (var v in Enumerable.Select(range.GetRowRange(row, RangeType.Static).GetNonEmptyCells(),
                   c => new KeyValuePair<int, object>(c.ColumnIndex, c.Value)))
                    yield return v;
            }
            else
            {
                for (int column = 0; column < array.ColumnCount; column++)
                    yield return new KeyValuePair<int, object>(column, array[row, column]);
            }
        }
        static IEnumerable<KeyValuePair<int, object>> GetColumnValues(IArray array, int column)
        {
            if (array is IRange)
            {
                IRange range = (IRange)array;
                foreach (var v in Enumerable.Select(range.GetColumnRange(column, RangeType.Static).GetNonEmptyCells(),
                   c => new KeyValuePair<int, object>(c.RowIndex, c.Value)))
                    yield return v;
            }
            else
            {
                for (int row = 0; row < array.RowCount; row++)
                    yield return new KeyValuePair<int, object>(row, array[row, column]);
            }
        }
        public static object HLookup([AccessCellValue(AcceptNull = true)] object lookup_value, IArray table_array,
                                     [AllowDoubleToIntegerConversion]int row_index_num, [Optional(true)] bool range_lookup)
            //\\add support for ? and *  //\\check sorted
        {
            if (lookup_value == null)
                return Errors.NaError;
            if (row_index_num < 1)
                return Errors.ValueError;
            if (row_index_num > table_array.RowCount)
                return Errors.RefError;
            IComparable bestValue = double.NaN;
            int bestIndex = -1;
            IEnumerable<KeyValuePair<int, object>> values = GetRowValues(table_array, 0);
        //    IRange valueRange = table_array.GetRowRange(0);
            foreach (var pair in values)
            {
                if (pair.Value == null)
                    continue;
                if (pair.Value.GetType() != lookup_value.GetType())
                    continue;
                if (pair.Value is IComparable)
                {
                    var comparable = (IComparable) pair.Value;
                    if (comparable.CompareTo(lookup_value) == 0)
                    {
                        bestIndex = pair.Key;
                        break;
                    }
                    if (range_lookup)
                    {
                        if (!(bestValue is double) || !double.IsNaN((double) bestValue))
                        {
                            if (comparable.CompareTo(lookup_value) < 0 && bestValue.CompareTo(comparable) < 0)
                            {
                                bestValue = comparable;
                                bestIndex = pair.Key;
                            }
                        }
                        else
                        {
                            bestValue = comparable;
                            if (bestValue.CompareTo(lookup_value) < 0)
                                bestIndex = pair.Key;
                        }
                    }
                }
            }
            if (bestIndex >= 0)
            {
                if (table_array is IRange)
                {
                    IRange r = (IRange) table_array;
                    return r[row_index_num - 1, bestIndex - r.ColumnIndex].Value;
                }
                else
                {
                    return table_array[row_index_num - 1, bestIndex];
                }
            }
            return Errors.ValueError;
        }

        public static object Hyperlink([WorksheetContext]IWorksheet worksheet, string location, [Optional(null)] string friendly_name)
        {
            return worksheet.CreateHyperlink(location, friendly_name);
           
        }

        public static object Index([CellContext]ICell context, [AllowCellToRangeConversion] object array, [Optional(null)] int? row_num,
                                   [Optional(null)] int? column_num, [Optional(null)]int?area_num) //\\ add support for zero index
        {
            if (array is ICell)
                array = ((ICell) array).ToRange();
            if (array is IRange)
            {
                IRange r = (IRange) array;
                if (area_num != null)
                    array = r.GetArea(area_num.Value - 1);
            }
            IArray a = (IArray) array;
            int actualRow = row_num.GetValueOrDefault(1);

            int actualColumn = column_num.GetValueOrDefault(1);
            if (actualRow < 0 || actualColumn < 0)
                return Errors.ValueError;
            if (actualRow == 0)
            {
                if (actualColumn == 0)
                    return a;
                else
                {
                    return new SubArray(a, null, actualColumn - 1);
                }
            }
            Contract.Assert(actualRow > 0);
            Contract.Assert(actualColumn > 0);
            if (actualColumn == 0)
                return new SubArray(a, actualRow - 1, null);
            if (actualRow <= a.RowCount && actualColumn <= a.ColumnCount)
            {
                if (a is IRange)
                {
                    IRange r = (IRange) a;
                    var cell = r.Worksheet.GetCell(r.RowIndex + actualRow-1, r.ColumnIndex + actualColumn-1);
                    return cell;
                }
                return new RelativeArray(a, actualRow - 1, actualColumn - 1);
            }
            return Errors.RefError;
        }
        class SubArray: IArray
        {
            private readonly IArray _inner;
            private readonly int? _row;
            private readonly int? _column;

            public SubArray(IArray inner, int? row, int? column)
            {
                Contract.Requires(inner != null);
                Contract.Requires(!(row == null && column == null));
                Contract.Requires(row.GetValueOrDefault(0) >= 0);
                Contract.Requires(column.GetValueOrDefault(0) >= 0);
                _inner = inner;
                _row = row;
                _column = column;
            }

            public IEnumerator<object> GetEnumerator()
            {
                for (int r = 0; r < RowCount; r++)
                {
                    for (int c = 0; c < ColumnCount; c++)
                        yield return this[r, c];
                }
            }

            IEnumerator IEnumerable.GetEnumerator()
            {
                return GetEnumerator();
            }

            public object this[int row, int column]
            {
                get { return _inner[row + _row.GetValueOrDefault(0), column + _column.GetValueOrDefault(0)]; }
            }

            public int RowCount
            {
                get
                {
                    return _row != null ? 1 : _inner.RowCount;
                }
            }

            public int ColumnCount
            {
                get { return _column != null ? 1 : _inner.ColumnCount; }
            }
        }
        class RelativeArray: IArray
        {
            private readonly IArray _inner;
            private readonly int _row;
            private readonly int _column;

            public RelativeArray(IArray inner, int row, int column)
            {
                _inner = inner;
                _row = row;
                _column = column;
            }

            public IEnumerator<object> GetEnumerator()
            {
                return _inner.GetEnumerator();
            }

            IEnumerator IEnumerable.GetEnumerator()
            {
                return GetEnumerator();
            }

            public object this[int row, int column]
            {
                get { return _inner[this._row + row, this._column + column]; }
            }

            public int RowCount
            {
                get { return _inner.RowCount - _row; }
            }

            public int ColumnCount
            {
                get { return _inner.ColumnCount - _column; }
            }
        }

        public static object Indirect([WorksheetContext] IWorksheet context, string ref_text, [Optional(true)] bool A1)
        {
            if (ref_text.Contains("!"))
                throw new NotImplementedException();
            if (!A1)
                throw new NotImplementedException();
            return context.GetRange(ref_text, A1);
        }
        public static object Lookup(IComparable lookup_value, IArray lookup_vector, [Optional(null)]IArray result_vector)
        {
            if (result_vector == null)
            {
                if (lookup_vector.ColumnCount > lookup_vector.RowCount)
                {
                    IArray newLookupVector = new FunctionArray((row, column) => lookup_vector[0, column],
                                                               1, lookup_vector.ColumnCount);
                    IArray new_ResultVector =
                        new FunctionArray((row, column) => lookup_vector[lookup_vector.RowCount - 1, column],
                                          1, lookup_vector.ColumnCount);
                    return Lookup(lookup_value, newLookupVector, new_ResultVector);
                }
                else
                {
                    IArray newLookupVector = new FunctionArray((row, column) => lookup_vector[row, 0],
                                                               lookup_vector.RowCount, 1);
                    IArray new_ResultVector =
                        new FunctionArray((row, column) => lookup_vector[row, lookup_vector.ColumnCount - 1],
                                          lookup_vector.RowCount, 1);
                    return Lookup(lookup_value, newLookupVector, new_ResultVector);
                    
                }

            }
            return Lookup2(lookup_value, lookup_vector, result_vector);
        }
        class FunctionArray: IArray
        {
            private readonly Func<int, int, object> _fn;
            private readonly int _rowCount;
            private readonly int _columnCount;

            public FunctionArray(StandardAddIn.Func<int, int, object>fn, int rowCount, int columnCount)
            {
                _fn = fn;
                _rowCount = rowCount;
                _columnCount = columnCount;
            }

            public IEnumerator<object> GetEnumerator()
            {
                throw new NotSupportedException();
            }

            IEnumerator IEnumerable.GetEnumerator()
            {
                return GetEnumerator();
            }

            public object this[int row, int column]
            {
                get { return _fn(row, column); }
            }

            public int RowCount
            {
                get { return this._rowCount; }
            }

            public int ColumnCount
            {
                get { return _columnCount;}
            }
        }
        
        static object Lookup2(IComparable lookup_value, IArray lookup_vector, IArray result_vector)
            //\\change to case insensitive
        {
            StandardAddIn.Func<int, object> lookupAccessor;
            StandardAddIn.Func<object, object> accessValue = (o => (o is ICell)?((ICell)o).Value: o);
            if (lookup_vector.RowCount == 1)
                lookupAccessor = (i => accessValue(lookup_vector[0, i]));
            else
                lookupAccessor = (i => accessValue(lookup_vector[i, 0]));
            StandardAddIn.Func<int, object> resultAccessor;
            if (result_vector.RowCount == 1)
                resultAccessor = (i => accessValue(result_vector[0, i]));
            else
                resultAccessor = (i => accessValue(result_vector[i, 0]));
            int surface = lookup_vector.RowCount * lookup_vector.ColumnCount;
            for (int i = 0; i < surface; i++)
            {
                object v = lookupAccessor(i);
                if (v != null)
                {
                    if (CellComparator.CompareValues(v, lookup_value) == 0)
                        return resultAccessor(i);
                }
            }
            bool foundValue = false;
            object bestValue = null;
            object maxValue  = null;
            for (int i = 0; i < surface; i++)
            {
                object v = lookupAccessor(i);
                if (v != null)
                {
                    if (CellComparator.CompareValues(v, lookup_value) <= 0)
                    {
                        if (!foundValue)
                        {
                            foundValue = true;
                            maxValue = v;
                            bestValue = resultAccessor(i);
                        }
                        else if (CellComparator.CompareValues(v, maxValue) >= 0)
                        {
                            maxValue = v;
                            bestValue = resultAccessor(i);
                        }
                    }
                }
            }
            if (foundValue)
                return bestValue;

            return Errors.NaError;
        }

        public static object Match(object lookup_value, IRange lookup_array, [Optional(1)] int match_type)
        {
            CellAccessor lookupAccessor;
            if (lookup_array.RowSpan == 1)
                lookupAccessor = (i => lookup_array[0, i]);
            else if (lookup_array.ColumnSpan == 1)
                lookupAccessor = (i => lookup_array[i, 0]);
            else
                return Errors.NaError;
            match_type = Math.Max(Math.Min(match_type, 1), -1);
            switch (match_type)
            {
                case 0:
                    int i = SearchExact(lookup_value, lookupAccessor, lookup_array.RowSpan*lookup_array.ColumnSpan);
                    if (i == -1)
                        return Errors.NaError;
                    return i + 1;
                case -1:
                case 1:
                    {
                        int ii = SearchLessThanOrEqual(lookupAccessor, lookup_value,
                                                             lookup_array.RowSpan*lookup_array.ColumnSpan, match_type);
                        return ii + 1;
                    }
                default:
                    throw new NotSupportedException();
            }
        }

        private static int SearchExact(object lookup_value, CellAccessor lookupAccessor, int length)
        {
            for (int i = 0; i < length; i++)
            {
                if (CellComparator.CompareValues(lookup_value, lookupAccessor(i).Value) == 0)
                    return i;
            }
            return -1;
        }

        private static int BinarySearchLessThanOrEqualObsolete(CellAccessor a, object value, int length, bool checkDescending)
        {
            int small = 0;
            int large = (length) - 1;
            object previousValue = null;
            while (small <= large)
            {
                int index = small + ((large - small) >> 1);
               object  newValue= a(index);
                if (checkDescending && previousValue != null)
                {
                    if (CellComparator.CompareValues(previousValue,value ) > 0)
                        throw new ErrorException(Errors.NaError);
                }
                int i = CellComparator.CompareValues(newValue, value);
                previousValue = newValue;
                if (i == 0)
                {
                    return index;
                }
                if (i < 0)
                {
                    small = index + 1;
                }
                else
                {
                    large = index - 1;
                }
            }
            return SearchLessThanOrEqual(a, value, length);
        }

        private static int SearchLessThanOrEqual (CellAccessor a, object value, int length, int matchType)
        {
            int? previousComparison = null;
            object previousValue = null;
            for (int i = 0; i < length; i++)
            {
                object currentValue = a(i).Value;
                int current = CellComparator.CompareValues(a(i).Value, value);
                if (current == 0)
                    return i;
                if (previousComparison != null)
                {
                    var previous = previousComparison.Value;
                    Contract.Assert(previous != 0);
                    if (matchType != 0)
                    {
                        if (previous != current)
                        {
                            if (current == matchType)
                                return i-1;
                        }
                    }
                    if (matchType != 0 && CellComparator.CompareValues(currentValue, previousValue) != matchType )
                        throw new ErrorException(Errors.NaError);
                }
                previousComparison = current;
                previousValue = currentValue;
            }
            return -1;

        }

        private static int SearchLessThanOrEqual(CellAccessor a, object value, int length)
        {
            for (int i = length - 1; i >= 0; i--)
            {
                int c = CellComparator.CompareValues(a(i).Value, value);
                if (c <= 0)
                    return i;
            }
            return -1;
        }

        [VolatileFunction]
        public static object Offset([NoConversion] object cell, double rows, double columns,
            [AccessCellValueAttribute, Optional(double.NaN)] double height, [AccessCellValueAttribute, Optional(double.NaN)] double width)
        {
            IRange range;
            ICell myCell;
            int actualHeight;
            int actualWidth;
            if (cell is ICell)
            {
                myCell = (ICell) cell;
                range = myCell.ToRange();
                actualWidth = double.IsNaN(width) ? 1 : (int) width;
                actualHeight = double.IsNaN(height) ? 1 : (int) height;
            }
            else if (cell is IRange)
            {

                range = (IRange) cell;
                myCell = range[0, 0];
                actualWidth = double.IsNaN(width) ? range.RowSpan : (int)width;
                actualHeight = double.IsNaN(height) ? range.ColumnSpan : (int)height;

            }
            else
            {
                return Errors.ValueError;
            }
            if (actualHeight < 1 || actualWidth < 1)
                return Errors.ValueError;
            try
            {
                if (actualHeight == 1 && actualWidth == 1)
                    return range[(int)rows, (int)columns].ToRange();
                //return (cell.Worksheet[cell.Address + new CellRelativeAddress(rows, columns)]).To;
                var topLeft = getOffsetOf(myCell, (int)rows, (int)columns);
                var bottomRight = getOffsetOf(myCell, (int)(rows + actualHeight - 1), (int)(columns) + (int)actualWidth - 1);
                return myCell.Worksheet.CreateRange
                    (topLeft, bottomRight);


            }
            catch (Exception)
            {
                return Errors.RefError;
            }
        }

        private static ICell getOffsetOf(ICell cell, int rows, int columns)
        {
            if (cell.RowIndex + rows < 0)
                throw new RefException();
            if (cell.ColumnIndex + columns < 0)
                throw  new RefException();
            return cell.Worksheet.GetCell(cell.RowIndex + rows, cell.ColumnIndex + columns);
        }



      

        
        
        [NoValueAccess]
        public static object Row([CellContext]ICell context, [AcceptsRangeFirstCell, Optional(null)] ICell cr)
        {
            if (cr == null)
            {
                return context.RowIndex+1;
            }
            return new FuncArray((row, column) => cr.RowIndex + 1 + row, int.MaxValue, 1);
        }

        [NoValueAccess]
        public static object Rows(IArray array)
        {
            return array.RowCount;
        }

        public static object Rtd()
        {
            throw new NotImplementedException();
        }


        /// <summary>
        /// Returns the transpose of a range
        /// </summary>
        /// <param name="range">Tde range to transpose</param>
        /// <returns></returns>
        public static IArray Transpose(IArray range)
        {
            Debug.WriteLine("Calling transpose");
            return new TransposedArray(range);
        }
        class TransposedArray: IArray
        {
            private IArray Range;

            public TransposedArray(IArray range)
            {
                this.Range = range;
            }

            public IEnumerator<object> GetEnumerator()
            {
                IEnumerable<object> r = (IEnumerable<object>) Range;
                foreach (var v in r)
                {
                    yield return v;
                }
            }

            IEnumerator IEnumerable.GetEnumerator()
            {
                return GetEnumerator();
            }

            public object this[int row, int column]
            {
                get
                {
                    if (row < 0 || column < 0)
                        return Errors.NaError;
                    if (row >= Range.ColumnCount|| column >= Range.RowCount)
                        return Errors.NaError;
                    return this.Range[column, row];
                }
            }

            public int RowCount
            {
                get { return
                    Range.ColumnCount;}
            }

            public int ColumnCount
            {
                get { return Range.RowCount;}
            }
        }

        public static object VLookup([AccessCellValue(AcceptNull=true)] IComparable lookup_value, IArray table_array,
                                     [AllowDoubleToIntegerConversion]int col_index_num, [Optional(true)] bool range_lookup)
        {
            if (lookup_value == null)
                return Errors.NaError;
            if (col_index_num < 1)
                return Errors.ValueError;
            if (col_index_num > table_array.ColumnCount)
                return Errors.RefError;
            IComparable bestValue = double.NaN;
            int bestIndex = -1;
            var values = GetColumnValues(table_array, 0);
            //IRange valueRange = table_array.GetColumnRange(0);
            foreach (var c in values)
            {
                if (c.Value == null)
                    continue;
                if (c.Value.GetType() != lookup_value.GetType())
                    continue;
                if (c.Value is IComparable)
                {
                    var comparable = (IComparable) c.Value;
                    if (comparable.CompareTo(lookup_value) == 0)
                    {
                        bestIndex = c.Key;
                        break;
                    }
                    if (range_lookup)
                    {
                        if (!(bestValue is double) || !double.IsNaN((double) bestValue))
                        {
                            if (comparable.CompareTo(lookup_value) < 0 && bestValue.CompareTo(comparable) < 0)
                            {
                                bestValue = comparable;
                                bestIndex = c.Key;
                            }
                        }
                        else
                        {
                            bestValue = comparable;
                            if (bestValue.CompareTo(lookup_value) < 0)
                                bestIndex = c.Key;
                        }
                    }
                }
            }
            if (bestIndex >= 0)
            {
                if (table_array is IRange)
                {
                    IRange range = (IRange) table_array;
                    //\\return bestValue;
                    return range[bestIndex - range.RowIndex, col_index_num - 1].Value;
                }
                else
                {
                    return table_array[bestIndex - 0, col_index_num - 1];
                }
            }
            return Errors.NaError;
        }

        #region Nested type: CellAccessor

        private delegate ICell CellAccessor(int i);

        #endregion
    }
}